package com.hhwy.framework.code.generation.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hhwy.framework.code.generation.beans.MetaData;
import com.hhwy.framework.code.generation.util.PropertyUtil;

/**
 * 数据库操作类
 * @author zhangbaojian
 * @version 1.0
 * @created 21-7月-2014 9:24:59
 */
public class DBContext {

	public static final Logger log = LoggerFactory.getLogger(DBContext.class);
	private Connection conn;
	private String driverClassName;
	private String url;
	private String username;
	private String password;

	public DBContext() {

		this.driverClassName = PropertyUtil.getValue("jdbc.driverClassName");
		this.url = PropertyUtil.getValue("jdbc.url");
		this.username = PropertyUtil.getValue("jdbc.username");
		this.password = PropertyUtil.getValue("jdbc.password");
		try {
			Class.forName(this.driverClassName).newInstance();
			conn = DriverManager.getConnection(this.url,this.username,this.password);
		} catch (Exception e) {
			log.error("连接数据库异常", e);
			throw new RuntimeException("连接数据库异常", e);
		}

	}

	/**
	 * 
	 * @param tableName
	 * @throws SQLException 
	 */
	public List<MetaData> getMetaDataList(String tableName){
		List<MetaData> metaDataList = new ArrayList<MetaData>();
		try {
			DatabaseMetaData dbmd = conn.getMetaData();
			Statement stmt = conn.createStatement();
			
			ResultSet rsTable = null;
			if(driverClassName.indexOf("mysql") > -1){
				rsTable = stmt.executeQuery("select table_comment from information_schema.tables where table_name ='" + tableName + "'");
			} else if(driverClassName.indexOf("oracle") > -1) {
				rsTable = stmt.executeQuery("select comments from user_tab_comments where table_name ='" + tableName + "'");
			}
			String tableComment = null;
			while(rsTable.next()) {
				tableComment = rsTable.getString(1);
			}
			ResultSet rs = dbmd.getColumns(null, null, tableName, null);
			List<String> sqlTypeList = new ArrayList<String>();
			while (rs.next()) {
				String columnName = rs.getString("COLUMN_NAME");
				String propertyName = this.getPropertyName(columnName);
				if (!("id".equals(propertyName) || "createUser".equals(propertyName)
						|| "updateUser".equals(propertyName)
						|| "createTime".equals(propertyName)
						|| "updateTime".equals(propertyName))) {
					MetaData metaData = new MetaData();
					metaData.setTableName(tableName);
					metaData.setTableComment(tableComment);
					metaData.setColumnName(rs.getString("COLUMN_NAME"));
					metaData.setPropertyName(propertyName);
					metaData.setUpperPropertyName(this.getUpperPropertyName(propertyName));
					metaData.setPropertyType(this.getPropertyType(rs.getString("TYPE_NAME")));
					metaData.setLength(String.valueOf(rs.getInt("COLUMN_SIZE")));
					metaData.setNull(rs.getInt("NULLABLE")==1 ? true: false);
					metaData.setComment(StringUtils.isEmpty(rs.getString("REMARKS")) ? null:rs.getString("REMARKS"));
					metaData.setImportPackage(this.getImportPackage(rs.getString("TYPE_NAME"), sqlTypeList));
					metaData.setTestValue(this.getTestValue(rs.getString("TYPE_NAME"), rs.getInt("COLUMN_SIZE")));
					metaDataList.add(metaData);
				}
			}
			rs.close();
			conn.close();
		} catch (SQLException e) {
			log.error("查询数据库异常", e);
			throw new RuntimeException("查询数据库异常", e);
		}
		return metaDataList;
	}

	private String getImportPackage(String sqlType, List<String> sqlTypeList) {
		if(!sqlTypeList.contains(sqlType)){
			if ("DATETIME".equals(sqlType) || "TIMESTAMP".equals(sqlType)){
				sqlTypeList.add(sqlType);
				return "java.sql.Timestamp";
			} else if ("DATE".equals(sqlType)){
				sqlTypeList.add(sqlType);
				return "java.util.Date";
			}
		}
		return null; 
	}

	private String getTestValue(String sqlType, int length) {
		String value = "junit test";
		length = value.length() > length ? length : value.length();
		if("VARCHAR".equals(sqlType) || "VARCHAR2".equals(sqlType) || "CHAR".equals(sqlType)){
			return "\"" + value.substring(0, length) + "\"";
		} else if ("DATE".equals(sqlType)){
			return "new Date()";
		} else if ("INT".equals(sqlType) || "LONG".equals(sqlType)){
			return "1";
		} else if ("DATETIME".equals(sqlType) || "TIMESTAMP".equals(sqlType)){
			return "new Timestamp(System.currentTimeMillis())";
		}
		return "\"0\""; 
	}

	private String getUpperPropertyName(String propertyName) {
		return propertyName.substring(0,1).toUpperCase() + propertyName.substring(1);
	}

	private String getPropertyName(String propertyName) {
		propertyName = propertyName.toLowerCase();
		String[] name = propertyName.split("_");
		propertyName = name[0];
		for (int i = 1; i < name.length; i++) {
			propertyName = propertyName + name[i].substring(0,1).toUpperCase() + name[i].substring(1);
		}
		return propertyName;
	}

	private String getPropertyType(String sqlType) {
		if("VARCHAR".equals(sqlType) || "VARCHAR2".equals(sqlType) || "CHAR".equals(sqlType)){
			return "String";
		} else if ("DATE".equals(sqlType)){
			return "Date";
		} else if ("INT".equals(sqlType) || "LONG".equals(sqlType)){
			return "Integer";
		} else if ("DATETIME".equals(sqlType) || "TIMESTAMP".equals(sqlType)){
			return "Timestamp";
		}
		return "String"; 
	}

	public Connection getConn() {
		return conn;
	}
	/**
	 * 
	 * @param newVal
	 */
	public void setConn(Connection newVal) {
		conn = newVal;
	}

	public String getDriverClassName() {
		return driverClassName;
	}

	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

}